package dao;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.Content;
import bean.Thanks;
import database.Database;

public class ThanksDao {
	public ArrayList<Thanks> getAllThanks() {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select id,thanks_title,thanks_date,scan_num from thanks order by thanks_date desc";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			ArrayList<Thanks> thanks_list = new ArrayList<Thanks>();
			while (rs.next()) {
				Thanks thanks = new Thanks();
				thanks.setId(rs.getInt(1));
				thanks.setThanks_title(rs.getString(2));
				thanks.setThanks_date(rs.getString(3).substring(0, 10));
				thanks.setScan_num(rs.getInt(4));
				thanks_list.add(thanks);
			}
			return thanks_list;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public ArrayList queryConditionThanks(String message, String date_start,
			String date_end) throws UnsupportedEncodingException {
		Connection conn = null;
		ArrayList<Thanks> thanks_list = new ArrayList<Thanks>();
		try {
			conn = Database.getConnection();
			String temp_sql = "";
			if (date_start != null && !"".equals(date_start)
					&& date_end != null && !"".equals(date_end)) {
				temp_sql = "and thanks_date between '" + date_start + "' and '"
						+ date_end + "'";
			}
			String sql = "select id,thanks_title,thanks_date,scan_num from thanks where thanks_title like '%"
					+ message + "%' " + temp_sql + " order by thanks_date desc";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				Thanks thanks = new Thanks();
				thanks.setId(rs.getInt(1));
				thanks.setThanks_title(rs.getString(2));
				thanks.setThanks_date(rs.getString(3).substring(0, 10));
				thanks.setScan_num(rs.getInt(4));
				thanks_list.add(thanks);
			}
			prep.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return thanks_list;
	}

	/**
	 * 
	 * @param title
	 * @param date
	 * @return
	 */
	public int deleteThanksItem(String id) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "delete from thanks where id = " + id;
			PreparedStatement prep = conn.prepareStatement(sql);
			int num = prep.executeUpdate();
			prep.close();
			return num;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;

	}

	public Content getContent(String id) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select id,thanks_title,data from thanks where id="
					+ id;
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();
			rs.next();
			Content content = new Content();
			content.setId(rs.getInt(1));
			content.setTitle(rs.getString(2));

			String data = rs.getString(3);
			if (data != null) {
				data = data.replaceAll("\r\n", "");
				data = data.replaceAll("\r", "");
				data = data.replaceAll("\n", "");
				data = data.replaceAll("\"", "'");
			}
			content.setData(data);

			prep.close();
			return content;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	public int updateContent(String id, String modifyTitle, String data) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "update thanks set thanks_title=? , data=? where id="
					+ id;
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, modifyTitle);
			prep.setString(2, data);
			int num = prep.executeUpdate();

			prep.close();
			return num;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}

	public int insertItem(String insertTitle, String data, String date) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "insert into thanks(thanks_title,thanks_date,data,scan_num) values(?,?,?,?);";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, insertTitle);
			prep.setString(2, date);
			prep.setString(3, data);
			prep.setInt(4, 0);
			int num = prep.executeUpdate();

			prep.close();
			return num;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}
}
